1

In the session, Anne calculated the distances to the closest hospitals located within North-Rhine Westphalia (NRW). Still, she did not show how she subsetted the original file, which contains all hospitals in Germany. What did she do? Subset the data file yourself by relying on the spatial information of the csv-file “hospital_points” and the polygon of North-Rhine Westphalia. How many hospitals are located within the borders of NRW?

You need two shapefiles for that: the point layer hospital_points in the data folder and a shapefile of NRW. For NRW, you can either use Stefans OSM syntax or use the “german_states” shapefile and filter on AGS=="05".

The default of st_join will leave you with a ‘left-join’ and returns a data object with all hospitals and matching district information for those which are located within NRW. You can reset the option to perform an ‘inner-join’ and keep only the observation which lay within the predefined area (st_join(x , y, join = "", left = FALSE)).

solution 

# load hospitals
hospitals <- read.csv("../data/hospital_points.csv", 
                      header = T, fill = T, sep = ",") %>%
            sf::st_as_sf(., coords = c("X", "Y"),
            crs = 3035)


#  use the OSM function provided by Stefan
nrw <-
  osmdata::getbb(
    "Nordrhein-Westfalen", 
    format_out = "sf_polygon"
  ) %>% 
  .$multipolygon %>% 
  sf::st_transform(3035)

# or import shapefile nrw
nrw <- sf::st_read(dsn = "../data",
                         layer = "GER_STATES",
                         quiet = T) %>%
                        sf::st_transform(.,3035) %>% 
                        filter( AGS == "05")

# spatial join
nrw_hospitals <-
  hospitals %>% 
  sf::st_join(., 
          # point layer nrw
          nrw, 
          # chose intersect or within
          join = sf::st_intersects,
          # option false will 
          # keep only the hospital 
          # which could be joined
          left = FALSE)

nrw_hospitals
## Simple feature collection with 343 features and 26 fields
## geometry type:  POINT
## dimension:      XY
## bbox:           xmin: 4039814 ymin: 3052783 xmax: 4277224 ymax: 3246338
## projected CRS:  ETRS89-extended / LAEA Europe
## First 10 features:
##     beds ADE GF BSG RS AGS       SDV_RS                 GEN  BEZ IBZ BEM NBD
## 288  568   2  4   1 05  05 051110000000 Nordrhein-Westfalen Land  20  --  ja
## 289  558   2  4   1 05  05 051110000000 Nordrhein-Westfalen Land  20  --  ja
## 290  365   2  4   1 05  05 051110000000 Nordrhein-Westfalen Land  20  --  ja
## 291  475   2  4   1 05  05 051110000000 Nordrhein-Westfalen Land  20  --  ja
## 292  439   2  4   1 05  05 051110000000 Nordrhein-Westfalen Land  20  --  ja
## 293  104   2  4   1 05  05 051110000000 Nordrhein-Westfalen Land  20  --  ja
## 294  641   2  4   1 05  05 051110000000 Nordrhein-Westfalen Land  20  --  ja
## 295  200   2  4   1 05  05 051110000000 Nordrhein-Westfalen Land  20  --  ja
## 296  226   2  4   1 05  05 051110000000 Nordrhein-Westfalen Land  20  --  ja
## 297 1161   2  4   1 05  05 051110000000 Nordrhein-Westfalen Land  20  --  ja
##     SN_L SN_R SN_K SN_V1 SN_V2 SN_G FK_S3 NUTS         RS_0    AGS_0        WSK
## 288   05    0   00    00    00  000     0  DEA 050000000000 05000000 2009-11-01
## 289   05    0   00    00    00  000     0  DEA 050000000000 05000000 2009-11-01
## 290   05    0   00    00    00  000     0  DEA 050000000000 05000000 2009-11-01
## 291   05    0   00    00    00  000     0  DEA 050000000000 05000000 2009-11-01
## 292   05    0   00    00    00  000     0  DEA 050000000000 05000000 2009-11-01
## 293   05    0   00    00    00  000     0  DEA 050000000000 05000000 2009-11-01
## 294   05    0   00    00    00  000     0  DEA 050000000000 05000000 2009-11-01
## 295   05    0   00    00    00  000     0  DEA 050000000000 05000000 2009-11-01
## 296   05    0   00    00    00  000     0  DEA 050000000000 05000000 2009-11-01
## 297   05    0   00    00    00  000     0  DEA 050000000000 05000000 2009-11-01
##          EWZ      KFL         DEBKG_ID                geometry
## 288 17912134 34112.32 DEBKGDL20000E6GR POINT (4095599 3127379)
## 289 17912134 34112.32 DEBKGDL20000E6GR POINT (4094348 3137304)
## 290 17912134 34112.32 DEBKGDL20000E6GR POINT (4099466 3133197)
## 291 17912134 34112.32 DEBKGDL20000E6GR POINT (4100738 3130402)
## 292 17912134 34112.32 DEBKGDL20000E6GR POINT (4096526 3129923)
## 293 17912134 34112.32 DEBKGDL20000E6GR POINT (4094841 3132032)
## 294 17912134 34112.32 DEBKGDL20000E6GR POINT (4101452 3130494)
## 295 17912134 34112.32 DEBKGDL20000E6GR POINT (4092521 3129609)
## 296 17912134 34112.32 DEBKGDL20000E6GR POINT (4094721 3127315)
## 297 17912134 34112.32 DEBKGDL20000E6GR POINT (4096628 3125783)

2

Did the operationalization of health care provision convince you? Don’t you think it might be more important how many hospitals are close to the respondents? To test this, we want to calculate the number of hospitals per district in North-Rhine Westphalia. Use the syntax below to prep the hospital data.

Earn extra points by counting not only the number of hospitals but also the sum of hospital beds within a district.

nrw_districts <- sf::st_read(dsn = "../data",
                            layer = "GER_DISTRICTS",
                            # quiet is optional if you
                            # not want to print the metainformation
                            quiet = T) %>% 
                    sf::st_transform(. , 3035) %>% 
                    dplyr::rename(., district_id = id) %>% 
                    # filter the districts of NRW
                    dplyr::filter( district_id >= 5000 & district_id < 6000 ) 

nrw_hospitals <-  
  nrw_hospitals %>% 
    # beds were character, now numeric
     dplyr::mutate(beds = as.numeric(beds)) %>%
    # replace NAs as zeros for simplification
     replace(., is.na(.), 0)

You need a as_tibble() data frame to use the functions group_by() and summarise().

The function n() allows summarising the total count of hospitals. sum(beds) for summarizing the bed total per district.

solution 

district_hospital_join <-
  nrw_hospitals %>% 
    # join the hospitals 
    # within districts
    sf::st_join(., nrw_districts, join = sf::st_within) %>% 
    # use as tibble to perform
    # group by & summarise
    dplyr::as_tibble() %>% 
    dplyr::group_by(district_id) %>% 
    dplyr::summarise(hospital_ct = n(), 
              hospital_bed_ct = sum(beds)) %>% 
    # left join the new information
    # to the original data frame
    left_join(nrw_districts,.)
## `summarise()` ungrouping output (override with `.groups` argument)
## Joining, by = "district_id"
district_hospital_join
## Simple feature collection with 53 features and 3 fields
## geometry type:  MULTIPOLYGON
## dimension:      XY
## bbox:           xmin: 4031313 ymin: 3029642 xmax: 4283898 ymax: 3269981
## projected CRS:  ETRS89-extended / LAEA Europe
## First 10 features:
##    district_id hospital_ct hospital_bed_ct                       geometry
## 1         5111          12            5080 MULTIPOLYGON (((4094868 314...
## 2         5112           8            4607 MULTIPOLYGON (((4092415 316...
## 3         5113          13            5619 MULTIPOLYGON (((4114154 316...
## 4         5114           4            2093 MULTIPOLYGON (((4080863 314...
## 5         5116           5            2164 MULTIPOLYGON (((4075096 313...
## 6         5117           2             911 MULTIPOLYGON (((4105756 315...
## 7         5119           3            1507 MULTIPOLYGON (((4103124 316...
## 8         5120           3            1020 MULTIPOLYGON (((4128163 312...
## 9         5122           3            1145 MULTIPOLYGON (((4117277 312...
## 10        5124           3            2124 MULTIPOLYGON (((4130282 313...